package pers.vic.util.excel.other;

import java.io.IOException;
import java.io.InputStream;
import java.math.BigDecimal;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import javax.xml.parsers.ParserConfigurationException;
import javax.xml.parsers.SAXParser;
import javax.xml.parsers.SAXParserFactory;

import org.apache.commons.lang3.StringUtils;
import org.apache.commons.logging.Log;
import org.apache.commons.logging.LogFactory;
import org.apache.poi.hssf.usermodel.HSSFDateUtil;
import org.apache.poi.openxml4j.opc.OPCPackage;
import org.apache.poi.ss.usermodel.DataFormatter;
import org.apache.poi.xssf.eventusermodel.ReadOnlySharedStringsTable;
import org.apache.poi.xssf.eventusermodel.XSSFReader;
import org.apache.poi.xssf.model.StylesTable;
import org.apache.poi.xssf.usermodel.XSSFCellStyle;
import org.apache.poi.xssf.usermodel.XSSFRichTextString;
import org.xml.sax.Attributes;
import org.xml.sax.InputSource;
import org.xml.sax.SAXException;
import org.xml.sax.XMLReader;
import org.xml.sax.helpers.DefaultHandler;



/**
 * Excel2007数据解析器
 *
 * @author 管厚明
 */
public class Excel2007Reader extends DefaultHandler {

	protected static Log logger = LogFactory.getLog(Excel2007Reader.class);
	
    // Table with unique strings
    private ReadOnlySharedStringsTable sharedStringsTable;

    // 总共解析多少列
    private final int minColumnCount = 0;

    // 单元数据类型，startElement中确认赋值，endElement中使用判断
//    private XSSFDataType nextDataType;
    private XSSFDataType nextDataType;
    // 判断是否值标签‘V’
    private boolean vIsOpen;

    // 当前解析列下标
    private int thisColumn = -1;
    // 已完成解析列最近下标
    private int lastColumnNumber = 0;

    // 单元格值内容临时存储
    private StringBuffer value = new StringBuffer();
    // 单行数据集合
    private List<String> rowlist = new ArrayList<String>();
    // 当前sheet
    private int sheetIndex = -1;
    // 单Sheet数据
    private List<String[]> sheetData = new ArrayList<String[]>();
    // 多Sheet数据
    private Map<Integer, List<String[]>> workData = new HashMap<Integer, List<String[]>>();

    private DataFormatter formatter;
    
    private short formatIndex;
    
    private String formatString;
    
    private StylesTable stylesTable;

    /**
     * 解析单Sheet内容
     *
     * @param strings
     * @param sheetInputStream
     * @throws IOException
     * @throws ParserConfigurationException
     * @throws SAXException
     */
    private void processSheet(ReadOnlySharedStringsTable strings, InputStream sheetInputStream) throws IOException,
            ParserConfigurationException, SAXException {
        sheetIndex++;
        this.sharedStringsTable = strings;
        InputSource sheetSource = new InputSource(sheetInputStream);
        SAXParserFactory saxFactory = SAXParserFactory.newInstance();
        SAXParser saxParser = saxFactory.newSAXParser();
        XMLReader sheetParser = saxParser.getXMLReader();
        sheetParser.setContentHandler(this);
        sheetParser.parse(sheetSource);
    }

    /**
     * 解析多Sheet内容
     *
     * @param fileStream
     * @param isMuti
     * @throws Exception
     */
    public void process(InputStream fileStream, boolean isMuti) throws Exception {
        OPCPackage xlsxPackage = OPCPackage.open(fileStream);
        ReadOnlySharedStringsTable sharedStringsTable = new ReadOnlySharedStringsTable(xlsxPackage);
        XSSFReader xssfReader = new XSSFReader(xlsxPackage);
        this.stylesTable = xssfReader.getStylesTable();
        this.setFormatter(new DataFormatter());
        XSSFReader.SheetIterator iter = (XSSFReader.SheetIterator) xssfReader.getSheetsData();
        while (iter.hasNext()) {
            InputStream stream = iter.next();
            processSheet(sharedStringsTable, stream);
            stream.close();
            List<String[]> data = new ArrayList<String[]>();
            data.addAll(sheetData);
            workData.put(sheetIndex, data);
            sheetData.clear();
            if (!isMuti) {// 如果只加载一个sheet内容，直接跳出循环
                break;
            }
        }
    }

    /**
     * 解析多Sheet内容
     *
     * @param fileStream
     * @param isMuti     是否加载多个sheet
     * @param sheetName
     * @throws Exception
     */
    public void process(InputStream fileStream, boolean isMuti, String sheetName) throws Exception {
        OPCPackage xlsxPackage = OPCPackage.open(fileStream);
        ReadOnlySharedStringsTable sharedStringsTable = new ReadOnlySharedStringsTable(xlsxPackage);
        XSSFReader xssfReader = new XSSFReader(xlsxPackage);
        this.stylesTable = xssfReader.getStylesTable();
        this.setFormatter(new DataFormatter());
        XSSFReader.SheetIterator iter = (XSSFReader.SheetIterator) xssfReader.getSheetsData();
        while (iter.hasNext()) {
            InputStream stream = iter.next();
            processSheet(sharedStringsTable, stream);
            stream.close();
            List<String[]> data = new ArrayList<String[]>();
            data.addAll(sheetData);
            if (iter.getSheetName().equals(sheetName)) {
                workData.put(0, data);
            }
            sheetData.clear();
            if (!isMuti) {// 如果只加载一个sheet内容，直接跳出循环
                break;
            }
        }
    }

    // Captures characters only if a suitable element is open.
    // Originally was just "v"; extended for inlineStr also.
    public void characters(char[] ch, int start, int length) throws SAXException {
        if (vIsOpen)
            value.append(ch, start, length);
    }

    /**
     * 获取当前列索引下标
     *
     * @param name
     * @return
     */
    private int nameToColumn(String name) {
        int column = -1;
        for (int i = 0; i < name.length(); ++i) {
            int c = name.charAt(i);
            column = (column + 1) * 26 + c - 'A';
        }
        return column;
    }

    /**
     * XML解析标签开始
     */
    public void startElement(String uri, String localName, String name, Attributes attributes) throws SAXException {
        if ("inlineStr".equals(name) || "v".equals(name)) {// v=> value
            vIsOpen = true;
            value.setLength(0);
        } else if ("c".equals(name)) {// c => cell
            vIsOpen = false;
            String r = attributes.getValue("r");
            int firstDigit = -1;
            for (int c = 0; c < r.length(); ++c) {
                if (Character.isDigit(r.charAt(c))) {
                    firstDigit = c;
                    break;
                }
            }
            thisColumn = nameToColumn(r.substring(0, firstDigit));
            this.nextDataType = XSSFDataType.NUMBER;
            String cellType = attributes.getValue("t");
            String cellStyleStr = attributes.getValue("s");
            if ("b".equals(cellType))
                nextDataType = XSSFDataType.BOOL;
            else if ("e".equals(cellType))
                nextDataType = XSSFDataType.ERROR;
            else if ("inlineStr".equals(cellType))
                nextDataType = XSSFDataType.INLINESTR;
            else if ("s".equals(cellType))
                nextDataType = XSSFDataType.SSTINDEX;
            else if ("str".equals(cellType))
                nextDataType = XSSFDataType.FORMULA;
            else if (cellStyleStr != null) {
//				if (cellStyleStr.equals("2")) {// 判断是否是日期
//					nextDataType = XSSFDataType.DATE;
//				}
                int styleIndex = Integer.parseInt(cellStyleStr);
                XSSFCellStyle style = stylesTable.getStyleAt(styleIndex);
                this.formatIndex = style.getDataFormat();
                this.setFormatString(style.getDataFormatString());
                if (formatIndex == 14 || formatIndex == 15 || formatIndex == 16 || formatIndex == 17) {
                    nextDataType = XSSFDataType.DATE;
                } else if (formatIndex == 18 || formatIndex == 19 || formatIndex == 20 || formatIndex == 21 ||
                        formatIndex == 45 || formatIndex == 46 || formatIndex == 47) {
                    nextDataType = XSSFDataType.TIME;
                } else if (formatIndex == 22) {
                    nextDataType = XSSFDataType.DATETIME;
                }
            }
        }

    }

    /**
     * XML解析标签结束
     */
    public void endElement(String uri, String localName, String name) throws SAXException {
        String thisStr = null;
        // v标识单元格值标签
        if ("v".equals(name)) {
            // Process the value contents as required.
            // Do now, as characters() may be called more than once
            switch (nextDataType) {
                case BOOL:
                    char first = value.charAt(0);
                    thisStr = first == '0' ? "FALSE" : "TRUE";
                    break;

                case ERROR:
                    thisStr = "\"ERROR:" + value.toString() + '"';
                    break;

                case FORMULA:
                    // A formula could result in a string value,
                    // so always add double-quote characters.
                    thisStr = '"' + value.toString() + '"';
                    break;

                case INLINESTR:
                    XSSFRichTextString rtsi = new XSSFRichTextString(value.toString());
                    thisStr = rtsi.toString();
                    break;

                case SSTINDEX:
                    String sstIndex = value.toString();
                    try {
                        int idx = Integer.parseInt(sstIndex);
                        XSSFRichTextString rtss = new XSSFRichTextString(sharedStringsTable.getEntryAt(idx));
                        thisStr = rtss.toString();
                    } catch (NumberFormatException ex) {
                    }
                    break;

                case NUMBER:
                    String n = value.toString();
                    BigDecimal bd = new BigDecimal(n);
                    thisStr = bd.setScale(4, BigDecimal.ROUND_UP).toString();
                    break;
                case DATE:
                    Date date = HSSFDateUtil.getJavaDate(Double.valueOf(value.toString()));
                    thisStr = new SimpleDateFormat("yyyy-MM-dd").format(date);
                    break;
                case DATETIME:
                    Date dateTime = HSSFDateUtil.getJavaDate(Double.valueOf(value.toString()));
                    thisStr = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss").format(dateTime);
                    break;
                case TIME:
                    Date time = HSSFDateUtil.getJavaDate(Double.valueOf(value.toString()));
                    thisStr = new SimpleDateFormat("HH:mm:ss").format(time);
                    break;
                default:
                    thisStr = "(TODO: Unexpected type: " + nextDataType + ")";
                    break;
            }
            // 判定已解析列下标与当前下标差异，超过1则表示前面有空单元格，自动填入空串
            for (int i = lastColumnNumber; i < thisColumn - 1; ++i) {
                rowlist.add(StringUtils.EMPTY);
            }
            rowlist.add(thisStr);
            // 更新已解析列下标
            if (thisColumn > -1) {
                lastColumnNumber = thisColumn;
            }

        } else if ("row".equals(name)) {// 标识当前行结束，行数据写入结果
            for (int i = lastColumnNumber; i < (this.minColumnCount); i++) {
                rowlist.add(StringUtils.EMPTY);
            }
            lastColumnNumber = -1;
            sheetData.add(rowlist.toArray(new String[]{}));
            rowlist.clear();
        }

    }

    /**
     * 获取数据(单Sheet)
     *
     * @param sheetIndex sheet下标
     * @return
     */
    public List<String[]> getSheetData(Integer sheetIndex) {
        return workData.get(sheetIndex);
    }

    /**
     * 获取数据(多Sheet)
     *
     * @return
     */
    public Map<Integer, List<String[]>> getSheetData() {
        return workData;
    }
    
    public String getFormatString() {
		return formatString;
	}

	public void setFormatString(String formatString) {
		this.formatString = formatString;
	}

	public DataFormatter getFormatter() {
		return formatter;
	}

	public void setFormatter(DataFormatter formatter) {
		this.formatter = formatter;
	}


	
}